package com.sunda.spmsoversea.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.sunda.spmsoversea.dto.WerksAllStorageInvSearchDTO;
import com.sunda.spmsoversea.dto.WerksStorageInvSearchDTO;
import com.sunda.spmsoversea.dto.WhsStorageInvSearchDTO;
import com.sunda.spmsoversea.entity.WhsStorageInventory;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Repository;

import java.util.List;
import java.util.Map;

/**
 * <p>
 * 储位库存信息表 Mapper 接口
 * </p>
 *
 * @author Wayne
 * @since 2021-09-10
 */
@Mapper
@Repository("overseaWhsStorageInventoryMapper")
public interface WhsStorageInventoryMapper extends BaseMapper<WhsStorageInventory> {

    @Select("<script>" +
            "SELECT ROWNUM AS \"rowId\", RE.*\n" +
            "FROM (\n" +
            "         SELECT WSI.UUID AS \"uuid\",\n" +
            "                WSI.MATERIAL_NO AS \"materialNo\",\n" +
            "                WSI.QUANTITY AS \"quantity\",\n" +
            "                WSI.AVAILABLE_QTY AS \"availableQty\",\n" +
            "                MS.BASIC_UNIT AS \"basicUnit\",\n" +
            "                (CASE WHEN MS.UNIT_SALES IS NULL\n" +
            "                          THEN MS.BASIC_UNIT\n" +
            "                      ELSE MS.UNIT_SALES END) AS \"unitSales\",\n" +
            "                MS.MATERIAL_ZH_DESC AS \"materialZhDesc\",\n" +
            "                MS.MATERIAL_EN_DESC AS \"materialEnDesc\",\n" +
            "                MS.MOVEMENT_TYPE AS \"movementType\",\n" +
            "                WSI.CREATE_TIME AS \"createTime\",\n" +
            "                WSI.UPDATE_TIME AS \"updateTime\",\n" +
            "                WS.WERKS AS \"werks\",\n" +
            "                WS.WHS_LOCATION_CODE AS \"whsLocationCode\",\n" +
            "                WS.STORAGE_NO AS \"storageNo\",\n" +
            "                WS.STORAGE_AREA AS \"storageArea\"\n" +
            "         FROM WHS_STORAGE_INVENTORY WSI\n" +
            "                  LEFT JOIN WHS_STORAGE WS ON WSI.UUID = WS.UUID\n" +
            "                  LEFT JOIN MATERIAL_SAP MS ON WSI.MATERIAL_NO = MS.MATERIAL_NO\n" +
            "         WHERE WS.STORAGE_STATUS = '1' AND WSI.AVAILABLE_QTY > 0\n" +
            "           AND WS.WERKS = #{dto.werks}\n" +
            " <when test='dto.storageArea!=null and dto.storageArea != \"\"'> AND WS.STORAGE_AREA = #{dto.storageArea}</when>" +
            " <when test='dto.storageNo!=null and dto.storageNo != \"\"'> AND WS.STORAGE_NO = #{dto.storageNo}</when>" +
            " <when test='dto.searchInfo!=null and dto.searchInfo != \"\"'> " +
            "   AND INSTR(MS.MATERIAL_NO || ',' || MS.MATERIAL_ZH_DESC || ',' || MS.MATERIAL_EN_DESC, #{dto.searchInfo}, 1, 1) > 0" +
            " </when>" +
            "         ORDER BY WS.STORAGE_AREA, WS.STORAGE_NO, WSI.MATERIAL_NO\n" +
            "         ) RE" +
            "</script>")
    List<Map<String, Object>> getWerksStorageInvList(IPage iPage, @Param("dto")WerksStorageInvSearchDTO dto);

    @Select("<script>" +
            "SELECT ROWNUM AS \"rowId\", RE.*\n" +
            "FROM (\n" +
            "         SELECT WSI.UUID AS \"uuid\",\n" +
            "                WSI.MATERIAL_NO AS \"materialNo\",\n" +
            "                WSI.QUANTITY AS \"quantity\",\n" +
            "                WSI.AVAILABLE_QTY AS \"availableQty\",\n" +
            "                MS.BASIC_UNIT AS \"basicUnit\",\n" +
            "                (CASE WHEN MS.UNIT_SALES IS NULL\n" +
            "                          THEN MS.BASIC_UNIT\n" +
            "                      ELSE MS.UNIT_SALES END) AS \"unitSales\",\n" +
            "                MS.MATERIAL_ZH_DESC AS \"materialZhDesc\",\n" +
            "                MS.MATERIAL_EN_DESC AS \"materialEnDesc\",\n" +
            "                MS.MOVEMENT_TYPE AS \"movementType\",\n" +
            "                WSI.CREATE_TIME AS \"createTime\",\n" +
            "                WSI.UPDATE_TIME AS \"updateTime\",\n" +
            "                WS.WERKS AS \"werks\",\n" +
            "                WS.WHS_LOCATION_CODE AS \"whsLocationCode\",\n" +
            "                WS.STORAGE_NO AS \"storageNo\",\n" +
            "                WS.STORAGE_AREA AS \"storageArea\"\n" +
            "         FROM WHS_STORAGE_INVENTORY WSI\n" +
            "                  LEFT JOIN WHS_STORAGE WS ON WSI.UUID = WS.UUID\n" +
            "                  LEFT JOIN MATERIAL_SAP MS ON WSI.MATERIAL_NO = MS.MATERIAL_NO\n" +
            "         WHERE WS.STORAGE_STATUS = '1' AND WSI.AVAILABLE_QTY > 0\n" +
            "           AND WS.WERKS = #{dto.werks}\n" +
            " <when test='dto.storageArea!=null and dto.storageArea != \"\"'> AND WS.STORAGE_AREA = #{dto.storageArea}</when>" +
            " <when test='dto.storageNo!=null and dto.storageNo != \"\"'> AND WS.STORAGE_NO = #{dto.storageNo}</when>" +
            " <when test='dto.searchInfo!=null and dto.searchInfo != \"\"'> " +
            "   AND INSTR(MS.MATERIAL_NO || ',' || MS.MATERIAL_ZH_DESC || ',' || MS.MATERIAL_EN_DESC, #{dto.searchInfo}, 1, 1) > 0" +
            " </when>" +
            "         ORDER BY WS.STORAGE_AREA, WS.STORAGE_NO, WSI.MATERIAL_NO\n" +
            "         ) RE" +
            "</script>")
    List<Map<String, Object>> getWerksAllStorageInv(@Param("dto") WerksAllStorageInvSearchDTO dto);

    @Select("<script>" +
            "SELECT ROWNUM AS \"rowId\", RESULT.*\n" +
            "FROM (\n" +
            "     SELECT RE.WERKS AS \"werks\",\n" +
            "            RE.WHS_LOCATION_CODE AS \"whsLocationCode\",\n" +
            "            RE.MATERIAL_NO AS \"materialNo\",\n" +
            "            RE.\"totalQuantity\" AS \"totalQuantity\",\n" +
            "            (CASE WHEN SI.SAFETY_QTY_BASIC_UNIT IS NULL\n" +
            "                      THEN 0 ELSE SI.SAFETY_QTY_BASIC_UNIT END) AS \"safetyQtyBasicUnit\",\n" +
            "            MS.MATERIAL_ZH_DESC AS \"materialZhDesc\",\n" +
            "            MS.MATERIAL_EN_DESC AS \"materialEnDesc\",\n" +
            "            MS.BASIC_UNIT AS \"basicUnit\",\n" +
            "            MS.MOVEMENT_TYPE AS \"movementType\",\n" +
            "            (CASE WHEN MS.UNIT_SALES IS NULL\n" +
            "                      THEN MS.BASIC_UNIT ELSE MS.UNIT_SALES END) AS \"unitSales\"\n" +
            "     FROM (\n" +
            "              SELECT WS.WERKS,\n" +
            "                     WS.WHS_LOCATION_CODE,\n" +
            "                     WSI.MATERIAL_NO,\n" +
            "                     SUM(WSI.QUANTITY) AS \"totalQuantity\"\n" +
            "              FROM WHS_STORAGE_INVENTORY WSI\n" +
            "                       LEFT JOIN WHS_STORAGE WS ON WSI.UUID = WS.UUID\n" +
            "              WHERE WERKS != 'CN01'\n" +
            "              GROUP BY WS.WERKS, WS.WHS_LOCATION_CODE, WSI.MATERIAL_NO\n" +
            "          ) RE LEFT JOIN MATERIAL_SAP MS ON RE.MATERIAL_NO = MS.MATERIAL_NO\n" +
            "               LEFT JOIN SAFETY_INVENTORY SI ON SI.WERKS = RE.WERKS AND SI.MATERIAL_NO = RE.MATERIAL_NO\n" +
            "     WHERE RE.\"totalQuantity\" > 0\n" +
            "  <when test='dto.werks!=null and dto.werks != \"\"'> AND RE.WERKS = #{dto.werks}</when>" +
            "  <when test='dto.searchInfo!=null and dto.searchInfo != \"\"'> " +
            "    AND INSTR(MS.MATERIAL_NO || ',' || MS.MATERIAL_ZH_DESC || ',' || MS.MATERIAL_EN_DESC, #{dto.searchInfo}, 1, 1) > 0" +
            "  </when>" +
            "     ORDER BY RE.WERKS, RE.MATERIAL_NO\n" +
            " ) RESULT" +
            "</script>")
    List<Map<String, Object>> getStorageInvList(IPage iPage, @Param("dto") WhsStorageInvSearchDTO dto);

    @Select("SELECT ROWNUM AS \"rowId\", RE.*\n" +
            "FROM (\n" +
            "         SELECT WSI.UUID,\n" +
            "                WSI.MATERIAL_NO,\n" +
            "                WSI.QUANTITY,\n" +
            "                WSI.AVAILABLE_QTY,\n" +
            "                MS.BASIC_UNIT,\n" +
            "                MS.UNIT_SALES,\n" +
            "                MS.MATERIAL_ZH_DESC,\n" +
            "                MS.MATERIAL_EN_DESC,\n" +
            "                WSI.CREATE_TIME,\n" +
            "                WSI.UPDATE_TIME,\n" +
            "                WS.WERKS,\n" +
            "                WS.WHS_LOCATION_CODE,\n" +
            "                WS.STORAGE_NO,\n" +
            "                WS.STORAGE_AREA\n" +
            "         FROM WHS_STORAGE_INVENTORY WSI\n" +
            "                  LEFT JOIN WHS_STORAGE WS ON WSI.UUID = WS.UUID\n" +
            "                  LEFT JOIN MATERIAL_SAP MS ON WSI.MATERIAL_NO = MS.MATERIAL_NO\n" +
            "         WHERE WSI.QUANTITY > 0 AND WS.WERKS = #{werks}\n" +
            "           AND WSI.MATERIAL_NO = #{materialNo}\n" +
            "         ORDER BY WS.STORAGE_AREA, WS.STORAGE_NO\n" +
            "         ) RE")
    List<Map<String, Object>> getWhsStorageInventory(@Param("werks") String werks, @Param("materialNo") String materialNo);
}
